#!/usr/bin/python

# Project 1b
# Queries.sql

import os, sys, subprocess, getopt, re;
from subprocess import Popen, PIPE, STDOUT,call;

import urllib,urllib2;

# IMPORTANT IF THIS SCRIPT IS NOT RUN FROM ITS OWN DIRECTORY
#	saves caller's directory, changes to script's directory
#	at the end of script, changes back to caller's directory
caller_dir = os.getcwd()
script_dir = sys.path[0]
os.chdir(script_dir)

from helper_functions import *;    	# import helper functions from helper_functions.py
from config import *;        		# loads global variables from config.py
	
	
################ Global Variables ####################

USAGE = """UCLA-CS143 - Project 1B Grading Script

USAGE: main.py -s <submission_folder> -d <mysql_DB> -u <mysql_user> -id <student-id>
	--help
		Displays input options
	-s, --submissions <enter-location-here>
		(Optional)Provide location of student submission directory
	-d, --database <database-name>
		(Optional) Specify database to be used in mysql
	-u, --user <user-name>
		(Optional) Specify database user in mysql
	-n, --id <student-id>
		(Optional)System will test only student provided
	-t, --timeout <seconds>
		(Optional)Time out interval
	-q, --queryparameter <query_parameter>
	[ To view/change default values, please see the configuration file (config.py). ]

"""

#CHANGE GLOBAL VARIABLES BASED ON COMMAND-LINE INPUT
try:
	options, remainder = getopt.gnu_getopt(sys.argv[1:], 's:d:u:n:t:q:', ['submissions=', 'database=','user=','id=','timeout=','queryparameter=','help'])
except getopt.GetoptError, err:
	print str(err)
	print USAGE
	sys.exit(2)

for opt, arg in options:
	if opt in ('-s', '--submissions'):
		print "Changing submission directory to %s" % (arg)
		
		# drop forward slash at the end of string if present
		submission_folder = re.sub('/$', '', arg)
	
	elif opt in ('-d', '--database'):
		print "Changing mysql database to %s" % (arg)
		test_DB = arg
	
	elif opt in ('-u', '--user'):
		print "Changing mysql user to %s" % (arg)
		user = arg
	elif opt in ('-n', '--id'):
		student_id = arg
	elif opt in ('-t', '--timeout'):
		timeout = arg
	elif opt in ('-q', '--queryparameter'):
		query_parameter = arg
		enable_PHP_testing = 1
		
		# create results directory if doesn't exist
		if (not os.path.exists(test_submission_abs + php_test_folder)):
			os.system("echo password | sudo -S mkdir " + test_submission_abs + php_test_folder)
		
		header = "SID\tCREATE_SCORE\tLOAD_SCORE\tQRY_RUN_SCORE\tNUM_QUERY=3\tCASE_QUERY1\tCASE_QUERY2\tPHP\tTotal(70)\tCOMMENTS\n"
	elif opt == '--help':
		print USAGE
		sys.exit(2)
	else:
		print "option %s with value %s is not recognized."	% (opt, arg)
		assert False, "unhandled option"

################ END - Global Variables ####################


### CLEAN UP
# remove previous result file
if (os.path.exists(result_file)):
	os.remove(result_file)
 
# remove previous php test files
os.system("echo 'password' | sudo -S rm -f " + test_submission_abs + php_test_folder + "*")
	
### PREPARE FOLDERS  
if (not os.path.exists(results_dir)):
	os.mkdir(results_dir)

# based on selected test MySQL database, generate correct drop/create database command
fd_drop = open( drop_DB_script,'w')
output_str = "drop database if exists " + test_DB + ";"
fd_drop.write(output_str)
fd_drop.close()

fd_create = open( create_DB_script,'w')
output_str = "create database " + test_DB + ";"
fd_create.write(output_str)
fd_create.close()

cmd_drop_DB = "mysql -u %s < %s"	% (user, drop_DB_script)
cmd_create_DB = "mysql -u %s < %s"	% (user, create_DB_script)


### START GRADING PROCESS 
start_time = time.time()
print "=== Starting Auto Grading ===" 
# store the list of student submission
print "=== Checking Student Submissions ==="
student_list = []
if(student_id == 0):
	student_list = listdirs(submission_folder)
	student_list.sort()
else:
	student_list.append(student_id)
print "Number of directories (submissions) found: ", len(student_list)

# create output file for script results
resultsFile = open(result_file, 'w') or sys.exit("Unable to open output Gradebook file: " + result_file)

# read grader's test query1 and query2
q1 = open(query1_file, 'r') or sys.exit("Unable to open grader's query 1: " + query1_file)
test_query1 = q1.read()
q1.close()

q2 = open(query2_file, 'r') or sys.exit("Unable to open grader's query 2: " + query2_file)
test_query2 = q2.read()
q2.close()

# read grader's results for correct query1/query2 run
f1 = open(graders_query1_output, 'r') or sys.exit("Unable to open grader's query 1 solution file: " + graders_query1_output)
f1.readline()			# skip first line which lists field names
test_query_sol1 = set(f1.read().splitlines())
for line in test_query_sol1:
	if not line.strip():
		test_query_sol1.remove(line)
f1.close()

f2 = open(graders_query2_output, 'r') or sys.exit("Unable to open grader's query 2 solution file: " + graders_query2_output)
f2.readline()			# skip first line which lists field names
test_query_sol2 = set(f2.read().splitlines())
for line in test_query_sol2:
	if not line.strip():
		test_query_sol2.remove(line)
f2.close()



# PREPARE LOCAL VARIABLES
create_script_results = []
load_script_results = []
query_script_results = []
query1_script_results = []
query2_script_results = []
query3_script_results = []

sys.stdout.flush()

resultsFile.write(header)
#header = "SID\tCREATE_SCORE\tLOAD_SCORE\tQUERY_RUN_SCORE\tSAMPLE\tCASE_QUERY1\tCASE_QUERY2\tTotal\tCOMMENTS\n"
print "=== Starting auto grading ==="
for sid in student_list:
	sys.stdout.flush()
	                                                     
	print "== Restarting Mysql =="                         
	os.system("echo 'password' | sudo -S rm /var/log/mysql/* ")
	os.system("echo 'password' | sudo -S /etc/init.d/mysql restart ")
	
	current_start = time.time()
	# placeholder variables for gradebook file output
	points_array = []
	# create/load/query_run/num_query/query1/query2
	for i in range(0,6):
		points_array.append(int(0))
		
	if(enable_PHP_testing == 1):
		for i in range(0,len(php_test_query)):
			points_array.append(int(0))
	
	comments = ""
	cmd_create = "mysql -u %s %s < %s/%s/%s"	% (user, test_DB, submission_folder, sid, create_script)
	cmd_load = "mysql -u %s %s < %s/%s/%s"	% (user, test_DB, submission_folder, sid, load_script)
	cmd_query = "mysql -u %s %s < %s/%s/%s"	% (user, test_DB, submission_folder, sid, query_script)
	
	print "== " + sid + " START ==" 
	
	# 1. drop database test_DB if it exists
	if (subprocess.call(cmd_drop_DB, shell=True) != 0):
		create_script_results.append([sid, "0", "ERROR: unable to drop database"])
		error_str = "Error: Unable to drop database ", test_DB," for SID: ", sid
		print error_str
		
		# write results to gradebook and continue to next SID
		write_gradebook(resultsFile,sid,points_array,error_str,enable_PHP_testing)
		continue
	
	#print "Database ", test_DB," removed"
	sys.stdout.flush()
	
	# 2. create database test_DB
	if (subprocess.call(cmd_create_DB, shell=True) != 0):
		create_script_results.append([sid, "0", "ERROR: unable to create database"])
		error_str =  "Error: Unable to create database ", test_DB," for SID: ", sid
		print error_str
		
		# write results to gradebook and continue to next SID
		write_gradebook(resultsFile,sid,points_array,error_str,enable_PHP_testing) 
		continue
	
	#print "Database ", test_DB, " created"
	sys.stdout.flush()
	
	##################### RUN CREATE SCRIPT #####################
	try:
		print "= Testing ", create_script, " ="
		
		(create_failed,stdout, stderr, retcode) = runCmd(cmd_create,timeout) 
		
		#returncode is 0 if no error, 1 if error, less than 0 if terminated by signal
		if retcode == 0:
			create_failed = 0
			create_script_results.append([sid, "1", "passed"])
		else:
			create_failed = 1
			if retcode < 0:
				print "ERROR: Child was terminated by signal", -retcode
				create_script_results.append([sid, "0", "ERROR: child process terminted by signal"])
				comments += " Error when executing create script;"
			else:
				print "ERROR: returncode: ", retcode
				create_script_results.append([sid, "0", repr(stderr)])
				comments += " Create Script: Timeout;"

	
	except OSError, e:
		create_failed = 1
		print >>sys.stderr, "ERROR: Execution failed:", e
		comments += " Error when executing create script;"
	
	if (create_failed):
		print "Create Script: Failed - 0 points."
	else:
		print "Create Script: Successful - ", create_pts ," points."
		#Create score
		points_array[0] += create_pts

	
	sys.stdout.flush()
	                       
	##################### RUN LOAD SCRIPT #####################
	# if create script failed => error in load script
	if (create_failed):
		load_failed = 0
		print "Load  Script not Executed because Create Script FAILED"
		print "Query Script not Executed because Create Script FAILED"
		
		comments += " Load  Script not Executed because Create Script FAILED; Query Script not Executed because Create Script FAILED;"
		
		load_script_results.append([sid, "0", "ERROR: " + create_script + " script failed"])
		query1_script_results.append([sid, "0", "ERROR: " + create_script + " script failed"])
		query2_script_results.append([sid, "0", "ERROR: " + create_script + " script failed"])
		query3_script_results.append([sid, "0", "ERROR: " + create_script + " script failed"])
		
		# write results to gradebook and continue to next SID
		write_gradebook(resultsFile,sid,points_array,comments,enable_PHP_testing) 
		continue
	
	else:
	# run load script and store results
		try:
			print "= Testing ", load_script, " ="
		    
			(load_failed,stdout, stderr, retcode) = runCmd(cmd_load,timeout)
			
			#returncode 0 if no error, 1 if error, less than 0 if terminated by signal
			if retcode == 0:
				load_failed = 0
				load_script_results.append([sid, "1", "passed"])
			else:
				load_failed = 1
				if retcode < 0:
					print "ERROR: Child was terminated by signal", -retcode
					load_script_results.append([sid, "0", "ERROR: child process terminted by signal"])
					comments += " Error when executing load script;"
				else:
					print "ERROR: returncode: ", retcode
					load_script_results.append([sid, "0", repr(stderr)])
					comments += " Load Script: Timeout;"
				
		except OSError, e:
			load_failed = 1
			print >>sys.stderr, "ERROR: Execution failed:", e
			comments += " Error when executing load script;"
		
		if (load_failed):
			print "Load Script: Failed - 0 points."         
		else:
			print "Load Script: Successful - ", load_pts ," points."
			#Load score
			points_array[1] += load_pts
	
	sys.stdout.flush()      
	
	##################### TEST QUERIES #####################
	# if load script failed => error in query script
	if (load_failed):
		print "Query Script not Executed because Load Script FAILED"
		
		query1_script_results.append([sid, "0", "ERROR: " + load_script + " script failed"])
		query2_script_results.append([sid, "0", "ERROR: " + load_script + " script failed"])
		query3_script_results.append([sid, "0", "ERROR: " + load_script + " script failed"])
		
		comments += " Query Script not Executed because LOAD Script FAILED;"
		
		# write results to gradebook and continue to next SID
		write_gradebook(resultsFile,sid,points_array,comments,enable_PHP_testing) 
		continue
	
	elif (not create_failed and not load_failed):
	
	# run quert script and check if there is error
		try:
			print "= Testing ", query_script, " ="
			
			(query_failed,stdout, stderr, retcode) = runCmd(cmd_query,timeout)
		    
		    #returncode 0 if no error, 1 if error, less than 0 if terminated by signal
			if retcode == 0:
				query_failed = 0
				query_script_results.append([sid, "1", "passed"])
			else:
				query_failed = 1
				if retcode < 0:
					print "ERROR: Child was terminated by signal", -retcode
					query_script_results.append([sid, "0", "ERROR: child process terminted by signal"])
					comments += " Error when executing query script;"
				else:
					print "ERROR: returncode: ", retcode
					query_script_results.append([sid, "0", repr(stderr)])
					comments += " Query Script: Timeout;"

		except OSError, e:
			query_failed = 1
			print "ERROR: Execution failed:", e
			comments += " Error when executing query script;"
		
		if (query_failed):
			print "Query Script: Failed -10 points."
			#Query score
			points_array[2] += query_pts
	    
		print "= Runing Student Query 1 & 2 ="
		# Extract Student Query
		fail_extract = 1 # initialize variable
		try:
			# parse STUDENT's queries file (separate into individual queries)
			student_queries,fail_extract = extract_queries("%s/%s/%s" % ( submission_folder, sid, query_script), num_queries)
		except OSError, e:
		    print "ERROR: Execution failed:", e
		
		sys.stdout.flush()
		
		if (fail_extract):
			comments += " Cant parse query script file into separate queries - REQUIRED MANUAL GRADING;"
			points_array[3] += query_pts
		else:
			if (len(student_queries) < num_queries):
				points_array[3] += query_pts
			
			# run QUERY 1
			fail = 1	# reset variable
			try:
				if len(student_queries) > 0:
					
					tmpSQL = open(tmpSQL_file, 'w') or sys.exit("Unable to open tmp sql file: " + tmpSQL_file)
					tmpSQL.write(student_queries[0] + "\n") 
					tmpSQL.close()
					
					cmd = "mysql -u %s %s < %s"	% (user, test_DB, tmpSQL_file)
					fail, score, err_code = run_query(cmd, test_query_sol1, 1, timeout)
					
				else:
					print "Student Query 1: FAILED (no query found) - 0 points."
					comments += " Query 1 MISSING;"
			except OSError, e:
			    fail = 1
			    print >>sys.stderr, "ERROR: Execution failed:", e
			
			# assign/print score for student's query 1
			if (fail):
				if err_code == 1:
					print "Student Query 1: TIMEOUT - 0 points."
					comments += " Query 1 TIMEOUT;"
				elif len(student_queries) > 0:
					print "Student Query 1: FAILED - 0 points."
					comments += " Query 1 FAILED;"
			else:
				if(score == 1):
					# give credit for query running without errors
					points_array[4] += query1_pts
					print "Student Query 1: Executed Successfully - ", query1_pts ," points."
				else:
					comments += " Query 1 RESULT MISMATCHED;"
			
			sys.stdout.flush()
			
			# run QUERY 2
			fail = 1	# reset variable
			try:
				if len(student_queries) > 1:
					
					tmpSQL = open(tmpSQL_file, 'w') or sys.exit("Unable to open tmp sql file: " + tmpSQL_file)
					tmpSQL.write(student_queries[1] + "\n") 
					tmpSQL.close()
					
					cmd = "mysql -u %s %s < %s"	% (user, test_DB, tmpSQL_file)
					fail, score, err_code = run_query(cmd, test_query_sol2, 2, timeout)
					
				else:
					print "Student Query 2: FAILED (no query found) - 0 points."
					comments += " Query 2 MISSING;"
			except OSError, e:
			    fail = 1
			    print >>sys.stderr, "ERROR: Execution failed:", e
			
			# assign/print score for student's query 2
			if (fail):
				if err_code == 1:
					print "Student Query 2: TIMEOUT - 0 points."
					comments += " Query 2 TIMEOUT;"
				elif(len(student_queries) > 1):
					print "Student Query 2: FAILED - 0 points."
					comments += " Query 2 FAILED;"
			else:
				if(score == 1):
					# give credit for query running without errors
					points_array[5] += query2_pts
					print "Student Query 2: Executed Successfully - ", query2_pts ," points."
				else:
					comments += " Query 2 RESULT MISMATCHED;"
			
			sys.stdout.flush()
	
	else:
		comments += " Error Processing Submission"
	   
	
	# TEST PHP PAGE
	if (enable_PHP_testing == 1):
		# move the php page to www folder
		
		os.system("echo 'password' | sudo -S cp " + submission_folder + "/" + sid + "/" + php_test_file + " " + test_submission_abs + php_test_folder + sid +".php")
		os.system("echo 'password' | sudo -S chmod -R 755 " + test_submission_abs)
		
		test_file = sid + ".php"
		print "= Testing ", php_test_file, " ="
		if os.path.isfile(test_submission_abs + php_test_folder + test_file) :

			php_test_url_pre = submission_path + php_test_folder + test_file
			php_data = urllib2.urlopen(php_test_url_pre)
			php_orig_html = php_data.read()
			
			# OBTAIN QUERY PARAMETER
			m = re.search('textarea\s*\w*\s*name\s*=\s*"\w*"',php_orig_html,re.I)
			if(m):
				qp = re.sub(r'(?i)textarea\s*\w*\s*(?i)name\s*=\s*"', '', m.group(0), 0).strip()
				qp = re.sub('"', '', qp, 0).strip() 
			else:
				comments += 'WARNING: UNABLE TO GET QUERY PARAMETER(DEFAULT:' + query_parameter + ' used)'
				qp = query_parameter

			# OBTAIN METHOD TYPE
			if(re.search('method\s*=\s*"GET"',php_orig_html,re.I)):
				method_type = 'GET'
			else:
				method_type = 'POST'

			if(method_type == 'GET'):
				for idx in range(0,len(php_test_query)):

					# GET request
					php_test_url = php_test_url_pre + "?" + qp + "=" + urllib.quote(php_test_query[idx]) 
					php_data = urllib2.urlopen(php_test_url)

				#POST request
				#php_test_url = php_test_url_pre
				#php_data = urllib2.urlopen(php_test_url, param)

					s = php_data.read()

					if(php_test_query_sol[idx] == "error"):
						res = s.find("eval()'d code")
						if(res < 0):
							points_array[6+idx] = php_point
							print "Student PHP Page: Test " + str(idx+1) + " Executed Successfully - ", php_point ," points."
						else:
							print "Student PHP Page: Test " + str(idx+1) + " Failed."
					else:
						res = s.find(php_test_query_sol[idx])
						if(res >= 0):
							points_array[6+idx] = php_point
							print "Student PHP Page: Test " + str(idx+1) + " Executed Successfully - ", php_point ," points."
						else:
							print "Student PHP Page: Test " + str(idx+1) + " Failed."
			elif(method_type == 'POST'):
				for idx in range(0,len(php_test_query)):
                    
					# QUERT BUILDING
					param = urllib.urlencode({ qp : php_test_query[idx]})

					# POST request
					php_data = urllib2.urlopen(php_test_url_pre, param)

					s = php_data.read()

					if(php_test_query_sol[idx] == "error"):
						res = s.find("eval()'d code")
						if(res < 0):
							points_array[6+idx] = php_point
							print "Student PHP Page: Test " + str(idx+1) + " Executed Successfully - ", php_point ," points."
						else:
							print "Student PHP Page: Test " + str(idx+1) + " Failed."
					else:
						res = s.find(php_test_query_sol[idx])
						if(res >= 0):
							points_array[6+idx] = php_point
							print "Student PHP Page: Test " + str(idx+1) + " Executed Successfully - ", php_point ," points."
						else:
							print "Student PHP Page: Test " + str(idx+1) + " Failed."		

		else:
			comments += ' ERROR: php file does not exist;'
	   
	# DONE TEST PHP PAGE
    
	# write results to gradebook and continue to next SID
	write_gradebook(resultsFile,sid,points_array,comments,enable_PHP_testing) 
	print "[Time: ", round(time.time() - current_start,2) , "secs]"
	print "== " + sid + " DONE ==" 
	sys.stdout.flush()
	# done of test loop

#done of student loop 
resultsFile.close()
              
print "=== Project 1b grading is completed ===" 
total_time = int(time.time() - start_time)
print "Auto-Grader Execution Time: ", GetInHMS(total_time)
print "The result file written to ", result_file

# switch back to the caller's directory
os.chdir(caller_dir)
